using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Acms.SqlDal
{
    public abstract class Base
    {
        /// <summary>
        /// Fields avaiable in all classes.
        /// </summary>
        public enum BaseFields
        {
            VersionId
        }

        /// <summary>
        /// Check the connecton
        /// </summary>
        public bool IsSqlAvailable
        {
            get
            {
                bool retVal = false;

                try
                {
                    Connection.Open();
                    Connection.Close();
                    retVal = true;
                }
                catch (Exception e)
                {
                    System.Diagnostics.Trace.WriteLine("No data connection, exception: " + e.Message);
                }

                return retVal;
            }
        }

        /// <summary>
        /// Creates the Command Object Enforcing Naming converntion of ClassName_Action
        /// </summary>
        /// <param name="name">The Name of the Action eg GetAll (this assumes procedurename is of the format ClassName_GetAll)</param>
        /// <returns></returns>
        protected SqlCommand CreateCommand(string commandName)
        {
            SqlCommand tmp = new SqlCommand();
            string name = string.Concat("[acms].", this.GetType().Name, "_", commandName);
            if(name.Contains("Dal_"))
            {
                name = name.Replace("Dal_", "_");
            }
            tmp.CommandText = name;
            tmp.CommandType = System.Data.CommandType.StoredProcedure;
            return tmp;
        }

        /// <summary>
        /// Creates an input parameter with input direction and value assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <returns>Sql Parameter with input direction and value assigned</returns>
        protected void AddInputParameter(SqlCommand cmd, string name, object value, SqlDbType type)
        {
            AddInputParameter(cmd, name, value, type, null, null, null);
        }

        /// <summary>
        /// Creates an input parameter with input direction and value assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <param name="size">size of the parameter (pass null to use default)</param>
        /// <returns>Sql Parameter with input direction and value assigned</returns>
        protected void AddInputParameter(SqlCommand cmd, string name, object value, SqlDbType type, int? size)
        {
            AddInputParameter(cmd, name, value, type, size, null, null);
        }

        /// <summary>
        /// Creates an input parameter with input direction and value assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <param name="size">size of the parameter (pass null to use default)</param>
        /// <param name="scale">scale of the parameter (pass null to use default)</param>
        /// <param name="pression">pression of the parameter (pass null to use default)</param>
        /// <returns>Sql Parameter with input direction and value assigned</returns>
        protected void AddInputParameter(SqlCommand cmd, string name, object value, SqlDbType type, int? size, byte? pression, byte? scale)
        {
            AddParameter(cmd, name, type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.Input;

            bool isNull = false;
            if (value == null)
            {
                isNull = true;
            }
            else if (value is Guid && ((Guid)value) == Guid.Empty)
            {
                isNull = true;
            }
            else if (value is DateTime)
            {
                DateTime tmp = (DateTime)value;
                if (tmp == DateTime.MaxValue ||
                    tmp == DateTime.MinValue ||
                    tmp < new DateTime(1753, 1, 1, 12, 0, 0) ||
                    tmp > new DateTime(9999, 12, 31, 11, 59, 59)
                    )
                {
                    isNull = true;
                }
            }

            object tmpValue = null;
            if (!isNull)
            {
                //if an array convert to piped string list
                if (value is System.Xml.XmlDocument)
                {
                    System.Xml.XmlTextReader xmlReader = new System.Xml.XmlTextReader(new System.IO.StringReader(((System.Xml.XmlDocument)value).OuterXml));
                    tmpValue = new System.Data.SqlTypes.SqlXml(xmlReader);
                }
                else if (!(value is Byte[]) && value is Array)
                {
                    System.Text.StringBuilder tmp = new StringBuilder();
                    string spacer = "";
                    foreach (object s in (Array)value)
                    {
                        tmp.Append(spacer);
                        tmp.Append(s.ToString());
                        spacer = "|";
                    }
                    tmpValue = tmp.ToString();
                }
                else if (value is DateTime)
                {
                    tmpValue = ((DateTime)value).ToUniversalTime();
                }
                else if (value is System.Net.IPAddress)
                {
                    tmpValue = value.ToString();
                }
                else
                {
                    tmpValue = value;
                }
            }

            if (isNull)
            {
                cmd.Parameters[cmd.Parameters.Count - 1].Value = DBNull.Value;
            }
            else
            {
                cmd.Parameters[cmd.Parameters.Count - 1].Value = tmpValue;
            }
        }

        /// <summary>
        /// Creates an output parameter output direction assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <returns>Sql Parameter with output direction assigned but no value assigned</returns>
        protected void AddOutputParameter(SqlCommand cmd, string name, SqlDbType type)
        {
            AddOutputParameter(cmd, name, type, null, null, null);
        }

        /// <summary>
        /// Creates an output parameter output direction assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <param name="size">size of the parameter (pass null to use default)</param>
        /// <returns>Sql Parameter with output direction assigned but no value assigned</returns>
        protected void AddOutputParameter(SqlCommand cmd, string name, SqlDbType type, int? size)
        {
            AddOutputParameter(cmd, name, type, size, null, null);
        }

        /// <summary>
        /// Creates an output parameter output direction assigned
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <param name="size">size of the parameter (pass null to use default)</param>
        /// <param name="scale">scale of the parameter (pass null to use default)</param>
        /// <param name="pression">pression of the parameter (pass null to use default)</param>
        /// <returns>Sql Parameter with output direction assigned but no value assigned</returns>
        protected void AddOutputParameter(SqlCommand cmd, string name, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            AddParameter(cmd, name, type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.Output;
        }

        /// <summary>
        /// Adds Return Parameter to Command
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="type"></param>
        protected void AddReturnParameter(SqlCommand cmd, SqlDbType type)
        {
            AddReturnParameter(cmd, type, null, null, null);
        }

        /// <summary>
        /// Adds Return Parameter to Command
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="type"></param>
        protected void AddReturnParameter(SqlCommand cmd, SqlDbType type, int size)
        {
            AddReturnParameter(cmd, type, size, null, null);
        }

        /// <summary>
        /// Adds Return Parameter to Command
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="type"></param>
        protected void AddReturnParameter(SqlCommand cmd, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            AddParameter(cmd, "RetVal", type, size, scale, pression);
            cmd.Parameters[cmd.Parameters.Count - 1].Direction = ParameterDirection.ReturnValue;
        }

        /// <summary>
        /// Creates a parameter
        /// </summary>
        /// <param name="name">name of the parameter ignoring the @</param>
        /// <param name="type">Sql Type of the parameter</param>
        /// <param name="size">size of the parameter (pass null to use default)</param>
        /// <param name="scale">scale of the parameter (pass null to use default)</param>
        /// <param name="pression">pression of the parameter (pass null to use default)</param>
        /// <returns>Sql Parameter no direction or value assigned</returns>
        private void AddParameter(SqlCommand cmd, string name, SqlDbType type, int? size, byte? scale, byte? pression)
        {
            SqlParameter tmp = new SqlParameter();
            tmp.ParameterName = string.Concat("@", name);
            tmp.SqlDbType = type;

            if (size != null)
            {
                tmp.Size = size.Value;
            }

            if (scale != null)
            {
                tmp.Scale = scale.Value;
            }

            if (pression != null)
            {
                tmp.Precision = pression.Value;
            }

            cmd.Parameters.Add(tmp);
        }

        /// <summary>
        /// Executes a command and populates a Hashtable with the results. Ensures reader and connection are closed once completed or on error
        /// </summary>
        /// <param name="command">The command to execute</param>
        /// <returns>Hashtable of fields in first row</returns>
        protected Hashtable ExecuteGetItem(SqlCommand command)
        {
            SqlDataReader reader = ExecuteDataReader(command, CommandBehavior.SingleRow);
            if (reader != null)
            {
                try
                {
                    if (reader.Read())
                    {
                        return BaseReadRecord(reader); ;
                    }
                    else
                    {
                        return null;
                    }
                }
                finally
                {
                    if (transaction == null && command.Connection != null && command.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        command.Connection.Close();
                    }
                    if (!reader.IsClosed)
                    {
                        reader.Close();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        protected Hashtable[] ExecuteGetPagedCollection(SqlCommand command, int startIndex, int pageSize, ref int totalRecords)
        {
            AddInputParameter(command, "StartIndex", startIndex, SqlDbType.Int);
            AddInputParameter(command, "PageSize", pageSize, SqlDbType.Int);
            AddOutputParameter(command, "TotalRecordsOut", SqlDbType.Int);
            Hashtable[] tmp = ExecuteGetCollection(command);
            totalRecords = GetOutputValue<int>(command, "TotalRecordsOut");
            return tmp;
        }

        /// <summary>
        /// Executes a command and populates a Hashtable array with the results. Ensures reader and connection are closed once completed or on error
        /// </summary>
        /// <param name="command">The command to execute</param>
        /// <returns>Hashtable array of fields roe each row returned</returns>
        protected Hashtable[] ExecuteGetCollection(SqlCommand command)
        {
            SqlDataReader reader = ExecuteDataReader(command, CommandBehavior.SingleResult);
            if (reader != null)
            {
                List<Hashtable> tables = new List<Hashtable>();
                try
                {
                    while (reader.Read())
                    {
                        tables.Add(BaseReadRecord(reader));
                    }
                    return tables.ToArray();
                }
                finally
                {
                    if (transaction == null && command.Connection != null && command.Connection.State != System.Data.ConnectionState.Closed)
                    {
                        command.Connection.Close();
                    }
                    if (!reader.IsClosed)
                    {
                        reader.Close();
                    }
                }
            }
            else
            {
                return null;
            }
        }

        protected void ExecuteCommand(SqlCommand command)
        {
            //add return param
            AddReturnParameter(command, System.Data.SqlDbType.Int);
            //Execute Command
            ExecuteNonQuery(command);
            //if return param == -100 throw concurency exception
            int ret = GetReturnValue<int>(command);
            if (ret == -100)
            {
                if (transaction != null)
                {
                    RollbackTransaction();
                }
                throw new Exceptions.ConcurencyException();
            }
            else if (ret == -200)
            {
                if (transaction != null)
                {
                    RollbackTransaction();
                }
                throw new Exceptions.IdNotFoundException();
            }
        }

        /// <summary>
        /// Reads data that can exist in all classes into hashtale (eg VersionId field)
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Globalization", "CA1304:SpecifyCultureInfo", MessageId = "System.String.Compare(System.String,System.String,System.Boolean)")]
        protected Hashtable BaseReadRecord(SqlDataReader reader)
        {
            if (reader == null)
            {
                return null;
            }
            Hashtable table = new Hashtable();
            ReadRecord(table, reader);
            int i = -1;
            try
            {
                i = reader.GetOrdinal(BaseFields.VersionId.ToString());
            }
            catch (IndexOutOfRangeException)
            {
                try
                {
                    i = reader.GetOrdinal("ConcurencyId");
                }
                catch(IndexOutOfRangeException)
                {
                }
            }

            if (i >= 0)
            {
                GetReaderValue<byte[]>(i, BaseFields.VersionId, reader, table);
            }
            return table;
        }

        /// <summary>
        /// Reads data from a given row in the reader and populates a Hashtable using Fields enum
        /// </summary>
        /// <param name="reader">the reader set to the row to read</param>
        protected abstract void ReadRecord(Hashtable table, SqlDataReader reader);


        /// <summary>
        /// Executes a command returning a DataReader suring that the connection is closed if error occours
        /// </summary>
        /// <param name="command"></param>
        /// <returns></returns>
        private SqlDataReader ExecuteDataReader(SqlCommand command, CommandBehavior behavior)
        {
            command.Connection = Connection;
            try
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }
                else
                {
                    command.Connection.Open();
                }
                return command.ExecuteReader(behavior);
            }
            catch
            {
                if (transaction != null)
                {
                    command.Transaction.Rollback();
                }
                if (command.Connection != null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                connection.Dispose();
                connection = null;
                throw;
            }
        }

        /// <summary>
        /// Execute a scalar command
        /// </summary>
        /// <typeparam name="ScalarType">The return type</typeparam>
        /// <param name="command">The SQL command</param>
        /// <returns>The single result</returns>
        protected ScalarType ExecuteScalar<ScalarType>(SqlCommand command)
        {
            command.Connection = Connection;
            try
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }
                else
                {
                    command.Connection.Open();
                }
                return (ScalarType)command.ExecuteScalar();
            }
            catch
            {
                if (transaction != null)
                {
                    command.Transaction.Rollback();
                }
                if (command.Connection != null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                connection.Dispose();
                connection = null;
                throw;
            }
        }

        /// <summary>
        /// Executes a non query on the command ensuring that connection is closed on completion
        /// </summary>
        /// <param name="command"></param>
        private void ExecuteNonQuery(SqlCommand command)
        {
            command.Connection = Connection;
            try
            {
                if (transaction != null)
                {
                    command.Transaction = transaction;
                }
                else
                {
                    command.Connection.Open();
                }
                command.ExecuteNonQuery();
            }
            catch
            {
                if (command.Transaction != null)
                {
                    command.Transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (transaction == null && command.Connection != null && command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
            }
        }

        private SqlConnection connection;
        /// <summary>
        /// Singleton Sql Connection
        /// </summary>
        private SqlConnection Connection
        {
            get
            {
                if (connection == null)
                {
                    connection = new SqlConnection();
                    connection.ConnectionString = Properties.Settings.Default.AcmsConnString;
                }
                return connection;
            }
        }

        private SqlTransaction transaction;
        private Guid transactionKey;
        /// <summary>
        /// Starts a Sql trascation on the connection
        /// </summary>
        /// <returns>Guid key of the transaction</returns>
        public Guid StartTransaction()
        {
            try
            {
                Connection.Open();
                SqlTransaction transaction = Connection.BeginTransaction(IsolationLevel.ReadCommitted);
                this.transaction = transaction;
                transactionKey = AddTransaction(transaction);
                return transactionKey;
            }
            catch
            {
                if (Connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
                throw;
            }
        }

        /// <summary>
        /// Uses SqlTransaction matching key supplied
        /// </summary>
        /// <param name="transactionId"></param>
        public void UseTransaction(Guid transactionId)
        {
            transactionKey = transactionId;
            transaction = GetTransaction(transactionId);
            this.connection = transaction.Connection;
        }

        /// <summary>
        /// Saves SqlTransaction currently in use
        /// </summary>
        /// <param name="name">The name to signify the rollback point</param>
        public void SaveTransaction(string name)
        {
            if (transaction != null)
            {
                transaction.Save(name);
            }
        }

        /// <summary>
        /// Rollsback SqlTransaction currently in use
        /// </summary>
        public void RollbackTransaction()
        {
            if (transaction != null && transaction.Connection != null)
            {
                transaction.Rollback();
            }
            RemoveTransaction(transactionKey);
        }

        /// <summary>
        /// Rollsback SqlTransaction currently in use to a given point 
        /// </summary>
        public void RollbackTransaction(string saveName)
        {
            if (transaction != null)
            {
                transaction.Rollback(saveName);
            }
            RemoveTransaction(transactionKey);
        }

        /// <summary>
        /// Commits SqlTransaction currently in use
        /// </summary>
        public void CommitTransaction()
        {
            if (transaction != null)
            {
                try
                {
                    transaction.Commit();
                }
                finally
                {
                    if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                    {
                        transaction.Connection.Close();
                    }
                }

            }
            RemoveTransaction(transactionKey);
        }

        /// <summary>
        /// Commits SqlTransaction matching given Key
        /// </summary>
        public void CommitTransaction(Guid transactionKey)
        {
            SqlTransaction transaction = GetTransaction(transactionKey);
            if (transaction != null)
            {
                try
                {
                    transaction.Commit();
                }
                finally
                {
                    if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                    {
                        transaction.Connection.Close();
                    }
                }

            }
            RemoveTransaction(transactionKey);
        }

        /// <summary>
        /// Rollsback SqlTransaction matching given Key
        /// </summary>
        public void RollbackTransaction(Guid transactionKey)
        {
            SqlTransaction transaction = GetTransaction(transactionKey);
            if (transaction != null)
            {
                try
                {
                    transaction.Rollback();
                }
                catch(System.InvalidOperationException)
                {
                }
                finally
                {
                    if (transaction.Connection != null && transaction.Connection.State != ConnectionState.Closed)
                    {
                        transaction.Connection.Close();
                    }
                }

            }
            RemoveTransaction(transactionKey);
        }

        private Guid AddTransaction(SqlTransaction transaction)
        {
            Guid key = Guid.NewGuid();
            Transactions.Add(key, transaction);
            return key;
        }


        /// <summary>
        /// Gets transction from transaction store
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
        private SqlTransaction GetTransaction(Guid key)
        {
            if(Transactions.ContainsKey(key))
            {
                return Transactions[key];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Removes transaction from transaction store
        /// </summary>
        /// <param name="key"></param>
        private void RemoveTransaction(Guid key)
        {
            if (Transactions.ContainsKey(key))
            {
                Transactions.Remove(key);
            }
        }

        /// <summary>
        /// Transaction store
        /// </summary>
        private static System.Collections.Generic.Dictionary<Guid, SqlTransaction> Transactions = new Dictionary<Guid, SqlTransaction>();

        private T GetValue<T>(object val)
        {
            T retVal;
            if (val == DBNull.Value)
            {
                retVal = default(T);
            }
            else if (val is DateTime)
            {
                object local = DateTime.SpecifyKind((DateTime)val, DateTimeKind.Utc).ToLocalTime();
                return (T)local;    
            }
            else if (typeof(T) == typeof(System.Net.IPAddress))
            {
                retVal = (T)(object)System.Net.IPAddress.Parse(val as string);
            }
            else
            {
                retVal = (T)val;
            }
            return retVal;

        }

        /// <summary>
        /// Gets value from a data reader
        /// </summary>
        /// <typeparam name="T">type of value</typeparam>
        /// <param name="field">Enumerator field name</param>
        /// <param name="reader">Sql Data reader</param>
        /// <param name="table">Table to populate</param>
        protected void GetReaderValue<T>(Enum field, SqlDataReader reader, System.Collections.Hashtable table)
        {
            int i = -1;
            try
            {
                i = reader.GetOrdinal(field.ToString());
            }
            catch (IndexOutOfRangeException)
            {
                throw new Exceptions.LoadDataException(string.Format("Field '{0}' could not be found. Type = '{1}'", field.ToString(), field.GetType().FullName));
            }
            GetReaderValue<T>(i, field, reader, table);
        }

        protected void GetReaderValue<T>(int i, Enum field, SqlDataReader reader, System.Collections.Hashtable table)
        {
            if (i >= 0)
            {
                if (reader.GetProviderSpecificFieldType(i) == typeof(System.Data.SqlTypes.SqlXml))
                {
                    System.Data.SqlTypes.SqlXml val = reader.GetSqlXml(i);
                    if (!val.IsNull)
                    {
                        System.Xml.XmlDocument tmpDoc = new System.Xml.XmlDocument();
                        tmpDoc.Load(val.CreateReader());
                        table[field] = tmpDoc;
                    }
                    else
                    {
                        table[field] = null;
                    }
                }
                else
                {
                    object val;
                    T tmp;

                    val = reader.GetValue(i);
                    try
                    {
                        tmp = GetValue<T>(val);
                    }
                    catch (InvalidCastException)
                    {
                        throw new Exceptions.LoadDataException(string.Format("Field '{0}' in class '{1}' could not be cast to expected type '{2}'. Value Type = '{3}'. Value = '{4}'", field.ToString(), field.GetType().FullName, typeof(T).ToString(), val.GetType().ToString(), val.ToString()));
                    }
                    if (tmp != null)
                    {
                        table[field] = tmp;
                    }
                    else if (val == DBNull.Value && (tmp == null || tmp is Guid || tmp is DateTime))
                    {
                        table[field] = tmp;
                    }
                    else
                    {
                        throw new Exceptions.LoadDataException(string.Format("Field '{0}' in class '{1}' is DBNull but Value Type = '{2}' can not contain null", field.ToString(), field.GetType().FullName, typeof(T).ToString()));
                    }
                }
            }
        }

        protected T GetOutputValue<T>(SqlCommand command, string name)
        {
            T ret = default(T);
            if (command.Parameters[string.Format("@{0}", name)] != null)
            {
                try
                {
                    ret = GetParamValue<T>(command.Parameters[string.Format("@{0}", name)]);
                }
                catch (InvalidCastException)
                {
                    throw new Exceptions.LoadDataException(string.Format("Output Parameter '@{0}' From Command '{1}' cound not be cast to an '{2}'. Value returned '{3}' as '{4}'", name, command.CommandText, typeof(T).ToString(), command.Parameters[string.Format("@{0}", name)].Value, command.Parameters[string.Format("@{0}", name)].SqlDbType));
                }
            }
            return ret;
        }

        protected T GetReturnValue<T>(SqlCommand command)
        {
            T ret = default(T);
            if (command.Parameters["@RetVal"] != null)
            {
                ret = GetParamValue<T>(command.Parameters["@RetVal"]);
            }
            return ret;
        }

        private T GetParamValue<T>(SqlParameter p)
        {
            T ret = default(T);
            if (p != null)
            {
                ret = GetValue<T>(p.Value);
            }
            return ret;
        }


    }

}
